import pymysql
import json
from contextlib import closing
from db_utils import db_utils
import pymysql

class DataBiz:
    # def __init__(self):
        # 使用db_utils模块的全局配置，不再需要本地db_config
    
    def get_db_connection(self, db_name='lqetl'):
        """获取数据库连接"""
        try:
            conn, _ = db_utils.get_db_connection(db_name)
            return conn
        except Exception as e:
            print(f"数据库连接错误: {e}")
            return None
    
    def get_platforms(self):
        """从数据库获取所有平台"""
        try:
            with closing(self.get_db_connection()) as connection:
                if not connection:
                    return []
                
                with connection.cursor() as cursor:
                    sql = "SELECT DISTINCT k.plat_id, k.plat_name FROM kd_plat_shop k"
                    cursor.execute(sql)
                    platforms = cursor.fetchall()
                    
                    return [{'plat_id': row[0], 'plat_name': row[1]} for row in platforms]
        except Exception as e:
            print(f"获取平台数据错误: {e}")
            return []
    
    def get_stores_by_platform(self, platform_id):
        """根据平台ID获取店铺"""
        try:
            with closing(self.get_db_connection()) as connection:
                if not connection:
                    return []
                
                with connection.cursor() as cursor:
                    sql = """
                    SELECT DISTINCT k.plat_id, k.plat_name, k.shop_id, k.shop_name 
                    FROM kd_plat_shop k 
                    WHERE k.plat_id = %s
                    """
                    cursor.execute(sql, (platform_id,))
                    stores = cursor.fetchall()
                    
                    return [{'plat_id': row[0], 'plat_name': row[1], 'shop_id': row[2], 'shop_name': row[3]} for row in stores]
        except Exception as e:
            print(f"获取店铺数据错误: {e}")
            return []
    
    def validate_user(self, username, password):
        """验证用户登录信息"""
        try:
            with closing(self.get_db_connection()) as connection:
                if not connection:
                    return False
                
                with connection.cursor() as cursor:
                    sql = "SELECT * FROM kd_user WHERE name = %s AND password = %s"
                    cursor.execute(sql, (username, password))
                    user = cursor.fetchone()
                    
                    return user is not None
        except Exception as e:
            print(f"用户验证错误: {e}")
            return False

    def get_accounts(self):
        """从数据库获取所有账套"""
        try:
            with closing(self.get_db_connection()) as connection:
                if not connection:
                    return []
                
                with connection.cursor() as cursor:
                    sql = "SELECT id, name, acctID FROM kd_account ORDER BY name"
                    cursor.execute(sql)
                    accounts = cursor.fetchall()
                    
                    return [{'id': row[0], 'name': row[1], 'acctId': row[2]} for row in accounts]
        except Exception as e:
            print(f"获取账套数据错误: {e}")
            return []

    def get_tables(self):
        """从数据库获取所有数据表信息"""
        try:
            with closing(self.get_db_connection()) as connection:
                if not connection:
                    return []
                
                with connection.cursor() as cursor:
                    sql = "SELECT name, table_name FROM kd_tables where category='income' ORDER BY id asc"
                    cursor.execute(sql)
                    tables = cursor.fetchall()
                    
                    return [{'name': row[0], 'table_name': row[1]} for row in tables]
        except Exception as e:
            print(f"获取数据表信息错误: {e}")
            return []

    def get_cost_tables(self):
        """从数据库获取所有数据表信息"""
        try:
            with closing(self.get_db_connection()) as connection:
                if not connection:
                    return []
                
                with connection.cursor() as cursor:
                    sql = "SELECT name, table_name FROM kd_tables where category='cost' ORDER BY id asc"
                    cursor.execute(sql)
                    tables = cursor.fetchall()
                    
                    return [{'name': row[0], 'table_name': row[1]} for row in tables]
        except Exception as e:
            print(f"获取数据表信息错误: {e}")
            return []

    def get_operate_types(self):
        """从数据库获取所有操作类型（导入规则）"""
        try:
            with closing(self.get_db_connection()) as connection:
                if not connection:
                    return []
                
                with connection.cursor() as cursor:
                    sql = "SELECT id, name FROM kd_operate_type ORDER BY id"
                    cursor.execute(sql)
                    operate_types = cursor.fetchall()
                    
                    return [{'id': row[0], 'name': row[1]} for row in operate_types]
        except Exception as e:
            print(f"获取操作类型信息错误: {e}")
            return []

    def get_tasks(self, page=1, page_size=10, table_name=None, plat_id=None, shop_id=None, 
                   start_time_start=None, start_time_end=None, operate_type=None, run_flag=None, remark_like=None, category=None):
        """从kd_task表获取任务数据，支持分页和过滤条件"""
        try:
            with closing(self.get_db_connection()) as connection:
                if not connection:
                    return {'data': [], 'total': 0, 'page': page, 'page_size': page_size}
                
                with connection.cursor() as cursor:
                    # 构建基础查询
                    base_where = []
                    params = []
                    base_where2 = []
                    params2 = []
                    
                    # 添加过滤条件
                    if table_name:
                        base_where.append("t.tablename = %s")
                        params.append(table_name)
                        base_where2.append("t.tablename = %s")
                        params2.append(table_name)
                    
                    if plat_id:
                        base_where.append("t.plat_id = %s")
                        params.append(plat_id)
                        base_where2.append("t.plat_id = %s")
                        params2.append(plat_id)
                    
                    if shop_id:
                        base_where.append("t.shop_id = %s")
                        params.append(shop_id)
                        base_where2.append("t.shop_id = %s")
                        params2.append(shop_id)
                    
                    if start_time_start:
                        base_where.append("t.starttime >= %s")
                        params.append(f"{start_time_start} 00:00:00")
                        base_where2.append("t.starttime >= %s")
                        params2.append(f"{start_time_start} 00:00:00")
                    
                    if start_time_end:
                        base_where.append("t.starttime <= %s")
                        params.append(f"{start_time_end} 23:59:59")
                        base_where2.append("t.starttime <= %s")
                        params2.append(f"{start_time_end} 23:59:59")
                    
                    if operate_type:
                        base_where.append("t.operate_type = %s")
                        params.append(operate_type)
                        base_where2.append("t.operate_type = %s")
                        params2.append(operate_type)
                    
                    if run_flag:
                        base_where.append("t.run_flag = %s")
                        params.append(run_flag)
                        base_where2.append("t.run_flag = %s")
                        params2.append(run_flag)
                    
                    # 添加备注模糊查询条件
                    if remark_like:
                        base_where.append("t.remark LIKE %s")
                        params.append(f"%{remark_like}%")
                        base_where2.append("t.remark LIKE %s")
                        params2.append(f"%{remark_like}%")
                    
                    where_clause = ""
                    if base_where:
                        where_clause = "WHERE " + " AND ".join(base_where)

                    if category:
                        base_where2.append("tbl.category = %s")
                        params2.append(category)
                    where_clause2 = ""
                    if base_where2:
                        where_clause2 = "WHERE " + " AND ".join(base_where2)

                    # 获取总数
                    count_sql = f"SELECT COUNT(*) FROM kd_task t {where_clause}"
                    cursor.execute(count_sql, params)
                    total = cursor.fetchone()[0]
                    
                    # 获取分页数据，按id倒序排列
                    offset = (page - 1) * page_size
                    data_sql = f"""
                        SELECT t.id, t.tablename, tbl.name as table_display_name, t.plat_id, t.shop_id, 
                               t.starttime, t.endtime, t.operate_type, t.run_flag, t.last_run_time, t.remark
                        FROM kd_task t
                        LEFT JOIN kd_tables tbl ON t.tablename = tbl.table_name
                        {where_clause2}
                        ORDER BY t.id DESC 
                        LIMIT %s OFFSET %s 
                    """
                    params2.extend([page_size, offset])
                    cursor.execute(data_sql, params2)
                    tasks = cursor.fetchall()
                    
                    # 获取平台映射数据
                    plat_map = {}
                    try:
                        cursor.execute("SELECT DISTINCT plat_id, plat_name FROM kd_plat_shop")
                        for row in cursor.fetchall():
                            plat_map[row[0]] = row[1]
                    except:
                        pass
                    
                    # 获取店铺映射数据
                    shop_map = {}
                    try:
                        cursor.execute("SELECT plat_id, shop_id, shop_name FROM kd_plat_shop")
                        for row in cursor.fetchall():
                            key = f"{row[0]}_{row[1]}"
                            shop_map[key] = row[2]
                    except:
                        pass
                    
                    # 获取操作类型映射数据
                    operate_type_map = {}
                    try:
                        cursor.execute("SELECT id, name FROM kd_operate_type")
                        for row in cursor.fetchall():
                            operate_type_map[str(row[0])] = row[1]
                    except:
                        pass
                    
                    # 获取运行状态映射数据
                    run_flag_map = {}
                    try:
                        cursor.execute("SELECT id, name FROM kd_run_flag")
                        for row in cursor.fetchall():
                            run_flag_map[str(row[0])] = row[1]
                    except:
                        pass
                    
                    # 转换数据格式
                    result = []
                    for row in tasks:
                        plat_id = str(row[3]) if row[3] else ''
                        shop_id = str(row[4]) if row[4] else ''
                        
                        # 获取平台名称
                        plat_name = plat_map.get(plat_id, plat_id)
                        
                        # 获取店铺名称
                        shop_key = f"{plat_id}_{shop_id}" if plat_id and shop_id else ''
                        shop_name = shop_map.get(shop_key, shop_id)
                        
                        # 获取操作类型名称
                        operate_type_name = operate_type_map.get(str(row[7]), str(row[7])) if row[7] else '-'
                        
                        # 获取运行状态名称
                        run_flag_name = run_flag_map.get(str(row[8]), str(row[8])) if row[8] else '-'
                        
                        result.append({
                            'id': row[0],
                            'tablename': row[2] or row[1],  # 使用kd_tables的name，没有则使用tablename
                            'plat_name': plat_name or plat_id,  # 使用映射的平台名称，没有则使用plat_id
                            'shop_name': shop_name or shop_id,  # 使用映射的店铺名称，没有则使用shop_id
                            'starttime': str(row[5]) if row[5] else '-',
                            'endtime': str(row[6]) if row[6] else '-',
                            'operate_type': operate_type_name,  # 使用映射的操作类型名称
                            'run_flag': str(row[8]) if row[8] else '-',
                            'run_flag_name': run_flag_name,  # 使用映射的运行状态名称
                            'last_run_time': str(row[9]) if row[9] else '-',
                            'remark': row[10] or '-'  # 使用备注，没有则使用'-' 
                        })
                    
                    return {
                        'data': result,
                        'total': total,
                        'page': page,
                        'page_size': page_size,
                        'total_pages': (total + page_size - 1) // page_size
                    }
        except Exception as e:
            print(f"获取任务数据错误: {e}")
            return {'data': [], 'total': 0, 'page': page, 'page_size': page_size}

    def save_task(self, table_name, plat_id, shop_id, start_time, end_time, operate_type, remark=''):
        """保存任务到kd_task表"""
        try:
            # 为开始时间添加默认时分秒 00:00:00
            if start_time and len(start_time) == 10:  # 格式为 YYYY-MM-DD
                start_time = f"{start_time} 00:00:00"
            
            # 为结束时间添加默认时分秒 23:59:59
            if end_time and len(end_time) == 10:  # 格式为 YYYY-MM-DD
                end_time = f"{end_time} 23:59:59"
            
            with closing(self.get_db_connection()) as connection:
                if not connection:
                    return {'success': False, 'message': '数据库连接失败'}
                
                with connection.cursor() as cursor:
                    sql = """
                    INSERT INTO kd_task (tablename, plat_id, shop_id, starttime, endtime, operate_type, run_flag, remark) 
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
                    """
                    cursor.execute(sql, (table_name, plat_id, shop_id, start_time, end_time, operate_type, 10, remark))
                    connection.commit()
                    
                    return {'success': True, 'message': '保存成功'}
        except Exception as e:
            print(f"保存错误: {e}")
            return {'success': False, 'message': str(e)}

    def sync_kingdee_task(self, task_id):
        """同步金蝶任务，将kd_task的run_flag修改为20"""
        # 故意返回
        # return {'success': False, 'message': '故意返回'}

        try:
            with closing(self.get_db_connection()) as connection:
                if not connection:
                    return {'success': False, 'message': '数据库连接失败'}

                # 查询任务状态是否可运行
                with connection.cursor() as cursor:
                    query_sql = "SELECT run_flag FROM kd_task WHERE id = %s"
                    cursor.execute(query_sql, (task_id,))
                    task = cursor.fetchone()

                    #  提取当前run_flag，判断是否为可运行状态（run_flag=12或22）
                    #  增加22、23，即同步后可再提交同步请求
                    current_run_flag = task[0]
                    allow_run_flags = [12,22,23]

                    if current_run_flag not in allow_run_flags:
                        return {'success': False, 'message': f'任务ID={task_id}当前状态不可运行，当前状态为{current_run_flag}！'}

                with connection.cursor() as cursor:
                    # 更新kd_task表的run_flag为20
                    sql = """
                    UPDATE kd_task 
                    SET run_flag = 20 , last_run_time = NOW() 
                    WHERE id = %s 
                    and run_flag in (12,22,23)
                    """
                    result = cursor.execute(sql, (task_id,))
                    connection.commit()
                    
                    if result > 0:
                        return {'success': True, 'message': '金蝶同步成功启动'}
                    else:
                        return {'success': False, 'message': '未找到指定的任务'}
        except Exception as e:
            print(f"金蝶同步错误: {e}")
            return {'success': False, 'message': str(e)}